package com.scalar.chiptrack.adaptor.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;

import java.util.ArrayList;
import java.util.Iterator;

import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.Mailer;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.DateUtil;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.ScalarException;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.adaptor.AssemblyLot;
import com.scalar.chiptrack.adaptor.ShippedLot;
import com.scalar.chiptrack.adaptor.Adaptor;
import com.scalar.chiptrack.adaptor.costcode.CostCodeStatus;
import com.scalar.chiptrack.operations.process.dao.FabStage;
import com.scalar.chiptrack.operations.dataentry.InTransitLots;
import com.scalar.chiptrack.operations.packingslip.forms.PackingSlipForm;
import com.scalar.chiptrack.users.notification.dao.NotificationDAO;
import com.scalar.chiptrack.commons.Constants;

/**
 * Created by Chandra Shekar.
 * Date: May 23, 2005
 * Time: 3:30:16 PM
 */
public class AdaptorDAO implements AdaptorConstants
{
    private static Logger s_logger = null;

    public static  ArrayList<FabStage> getAllNotificationLotsList(String vendorName ) throws Exception
    {
        ArrayList<FabStage> newLotsList   = new ArrayList<FabStage>();
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        FabStage fabStage       = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select Part_Number, Lot_Number,Fab_Lot_Number,Vendor_Name,Process_Type,Notification_Name,Wafer_Qty, Die_Qty,Ship_Qty, Ship_To_Location, Ship_Die_Qty, CONVERT(VARCHAR, Ship_Date, 101 ) as Ship_Date ," +
                                            " Inventory_Type, Qty_out, Yield from Notification_Alert_Table where vendor_name = ? and Is_User_Notified = 1 order by Part_Number, Fab_Lot_Number " );

            pstmt.setString( 1, vendorName);
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                fabStage = new FabStage();
                fabStage.setDeviceName( rset.getString( "Part_Number" ) );
                fabStage.setLotNumber( rset.getString( "Lot_Number" ) );
                fabStage.setFabLotNumber( rset.getString( "Fab_Lot_Number" ) );
                fabStage.setVendorName( rset.getString("Vendor_Name").toUpperCase() );
                fabStage.setProcess( rset.getString( "Process_Type" ).toUpperCase() );
                fabStage.setNotificationName( rset.getString("Notification_Name") );
                fabStage.setWaferQtyIn( rset.getString("Wafer_Qty") );
                fabStage.setQtyIn( rset.getString( "Die_Qty" ) );
                fabStage.setShipQty( rset.getString( "Ship_Qty") );
                fabStage.setShipToLocation( rset.getString("Ship_To_Location") );
                fabStage.setShipDieQty( rset.getString("Ship_Die_Qty") );
                fabStage.setShipDate( rset.getString("Ship_Date") );
                fabStage.setSequence( rset.getString("Inventory_Type") );
                fabStage.setQtyOut( rset.getInt("Qty_out") );
                fabStage.setYield( rset.getFloat("Yield") );
                newLotsList.add( fabStage );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load all New Lots from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return newLotsList;
    }

    public static  ArrayList<FabStage> getAllNotificationLotsListByNsType(String nsType, boolean asOfDate ) throws Exception
    {
        ArrayList<FabStage> newLotsList   = new ArrayList<FabStage>();
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        FabStage fabStage       = null;

        try
        {
            conn = DataSource.getConnection();
            String query = "select DISTINCT Part_Number, Lot_Number,Fab_Lot_Number,Vendor_Name,Process_Type,Notification_Name,Wafer_Qty, Die_Qty,Ship_Qty, Ship_To_Location, Ship_Die_Qty, CONVERT(VARCHAR, Ship_Date, 101 ) as Ship_Date ," +
                           "Inventory_Type,Qty_out,yield from Notification_Alert_Table where Notification_Name = '" + nsType + "'" ;

            if ( asOfDate )
            {                     
                 query = query + " AND Timestamp between '" + DateUtil.getPreviousDate( 1, "MM/dd/yyyy HH:mm:ss") + "' AND '" +
                         DateUtil.getCurrentDate("MM/dd/yyyy HH:mm:ss") + "'";
            }
            else
            {
                query = query + " and Is_User_Notified = 1 ";
            }
            if (Constants.FT_LOW_YIELD_LOT_NOTIFICATION.equalsIgnoreCase(nsType) || Constants.WS_LOW_YIELD_LOT_NOTIFICATION.equalsIgnoreCase(nsType) )
            {
                query = query + " AND status= 'Open' order by Yield ";                
            }
            else
            {
                query = query + " AND status= 'Open' order by Part_Number, Fab_Lot_Number ";
            }
            
            /*pstmt = conn.prepareStatement( "select Part_Number, Lot_Number,Fab_Lot_Number,Vendor_Name,Process_Type,Notification_Name,Wafer_Qty, Die_Qty,Ship_Qty, Ship_To_Location, Ship_Die_Qty, CONVERT(VARCHAR, Ship_Date, 101 ) as Ship_Date ," +
                                            " Inventory_Type from Notification_Alert_Table where Notification_Name = ? and Is_User_Notified = 1 order by Part_Number, Fab_Lot_Number " );*/
            pstmt = conn.prepareStatement( query );
            //pstmt.setString( 1, nsType);
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                fabStage = new FabStage();
                fabStage.setDeviceName( rset.getString( "Part_Number" ) );
                fabStage.setLotNumber( rset.getString( "Lot_Number" ) );
                fabStage.setFabLotNumber( rset.getString( "Fab_Lot_Number" ) );
                fabStage.setVendorName( rset.getString("Vendor_Name").toUpperCase() );
                fabStage.setProcess( rset.getString( "Process_Type" ).toUpperCase() );
                fabStage.setNotificationName( rset.getString("Notification_Name") );
                fabStage.setWaferQtyIn( rset.getString("Wafer_Qty") );
                fabStage.setQtyIn( rset.getString( "Die_Qty" ) );
                fabStage.setShipQty( rset.getString( "Ship_Qty") );
                fabStage.setShipToLocation( rset.getString("Ship_To_Location") );
                fabStage.setShipDieQty( rset.getString("Ship_Die_Qty") );
                fabStage.setShipDate( rset.getString("Ship_Date") );
                fabStage.setSequence( rset.getString("Inventory_Type") );
                fabStage.setQtyOut( rset.getInt("Qty_out") );
                fabStage.setYield( rset.getFloat("Yield") );

                newLotsList.add( fabStage );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load all DropOut Lots for Notificatios from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return newLotsList;
    }

    // TO SEND THE NEW LOTS, NEW TRANSIT LOTS, NO PO LOTS, DISCREPANCY LOTS NOTIFICATIONS TO THE USER

   public static void sendAllNotifications(String vendorName, boolean notifyNewLots,
                                           boolean notifyNewShipLots, boolean notifyDisLots ) throws Exception
   {
        try
        {
            FabStage allnewLots         = null;
            ArrayList<FabStage> allNSLotsList     = getAllNotificationLotsList( vendorName );

            ArrayList<FabStage> fabnewLotList     = new ArrayList<FabStage>();
            ArrayList<FabStage> fabDisLotList     = new ArrayList<FabStage>();
            ArrayList<FabStage> fabTransitLotList = new ArrayList<FabStage>();
            ArrayList<FabStage> noPOfabLotList    = new ArrayList<FabStage>();

            ArrayList<FabStage> wbnewLotList      = new ArrayList<FabStage>();
            ArrayList<FabStage> wbDisLotList      = new ArrayList<FabStage>();

            ArrayList<FabStage> fabOpnewLotList   = new ArrayList<FabStage>();
            ArrayList<FabStage> fabOpDisLotList   = new ArrayList<FabStage>();

            ArrayList<FabStage> sortnewLotList    = new ArrayList<FabStage>();
            ArrayList<FabStage> sortDisLotList    = new ArrayList<FabStage>();
            ArrayList<FabStage> sortShipLotList   = new ArrayList<FabStage>();

            ArrayList<FabStage> dbnewLotList      = new ArrayList<FabStage>();
            ArrayList<FabStage> dbDisLotList      = new ArrayList<FabStage>();

            ArrayList<FabStage> assynewLotList    = new ArrayList<FabStage>();
            ArrayList<FabStage> assyDisLotList    = new ArrayList<FabStage>();
            ArrayList<FabStage> assyShipLotList   = new ArrayList<FabStage>();

            ArrayList<FabStage> preTestnewLotList = new ArrayList<FabStage>();
            ArrayList<FabStage> preTestDisLotList = new ArrayList<FabStage>();

            ArrayList<FabStage> ftnewLotList      = new ArrayList<FabStage>();
            ArrayList<FabStage> ftDisLotList      = new ArrayList<FabStage>();

            ArrayList<FabStage> balScannewLotList = new ArrayList<FabStage>();
            ArrayList<FabStage> balScanDisLotList = new ArrayList<FabStage>();

            ArrayList<FabStage> bakingnewLotList  = new ArrayList<FabStage>();
            ArrayList<FabStage> bakingDisLotList  = new ArrayList<FabStage>();

            ArrayList<FabStage> fponewLotList     = new ArrayList<FabStage>();
            ArrayList<FabStage> fpoDisLotList     = new ArrayList<FabStage>();

            ArrayList<FabStage> testnewLotList    = new ArrayList<FabStage>();
            ArrayList<FabStage> testDisLotList    = new ArrayList<FabStage>();
            ArrayList<FabStage> testShipLotList   = new ArrayList<FabStage>();

            ArrayList<FabStage> csnewLotList      = new ArrayList<FabStage>();
            ArrayList<FabStage> csDisLotList      = new ArrayList<FabStage>();

            ArrayList<FabStage> trnewLotList      = new ArrayList<FabStage>();
            ArrayList<FabStage> trDisLotList      = new ArrayList<FabStage>();

            ArrayList<FabStage> eolnewLotList     = new ArrayList<FabStage>();
            ArrayList<FabStage> eolDisLotList     = new ArrayList<FabStage>();
            ArrayList<FabStage> eolShipLotList    = new ArrayList<FabStage>();

            ArrayList<FabStage> markPackNewLotList= new ArrayList<FabStage>();
            ArrayList<FabStage> markPackDisLotList= new ArrayList<FabStage>();

            ArrayList<FabStage> fgnewLotList      = new ArrayList<FabStage>();
            ArrayList<FabStage> fgDisLotList      = new ArrayList<FabStage>();
            ArrayList<FabStage> fgShipLotList     = new ArrayList<FabStage>();

            ArrayList<FabStage> assemVendorList   = new ArrayList<FabStage>();
            ArrayList<FabStage> ftLowYieldList   = new ArrayList<FabStage>();
            ArrayList<FabStage> wsLowYieldList   = new ArrayList<FabStage>();
            //ArrayList dropOutLotList    = new ArrayList();


            ////GETTING ALL THE  PROCESS NOTIFICATION TYPE LOTS
            if( allNSLotsList != null && allNSLotsList.size() > 0 )
            {
                for( int i=0; i < allNSLotsList.size(); i++)
                {
                    allnewLots = (FabStage)allNSLotsList.get(i);

                    //DROP OUT LOTS NOTIFICATION

                    /*if ( Constants.DROPOUT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        dropOutLotList.add( allnewLots );
                    }*/
                    //FAB RELATED NOTIFICATION LOTS
                    if( LOCATION_TYPE_FAB.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.NEW_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        fabnewLotList.add( allnewLots );
                    }
                    else if( LOCATION_TYPE_FAB.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.DISCREPENCY_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        fabDisLotList.add( allnewLots);
                    }
                    else if( LOCATION_TYPE_FAB.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.NEW_TRANSIT_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        fabTransitLotList.add( allnewLots);
                    }
                    else if( LOCATION_TYPE_FAB.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.NO_PO_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        noPOfabLotList.add( allnewLots);
                    }

                    //WAFER STORE RELATED NOTIFICATION LOTS
                    else if( ( LOCATION_TYPE_WAFER_STORE.equalsIgnoreCase( allnewLots.getProcess() )  || LOCATION_TYPE_WAFER_BANK.equalsIgnoreCase( allnewLots.getProcess() ) ) && Constants.NEW_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        wbnewLotList.add( allnewLots );
                    }
                    else if( ( LOCATION_TYPE_WAFER_STORE.equalsIgnoreCase( allnewLots.getProcess() )  || LOCATION_TYPE_WAFER_BANK.equalsIgnoreCase( allnewLots.getProcess() ) ) && Constants.DISCREPENCY_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        wbDisLotList.add( allnewLots );
                    }

                     //BUMP RELATED NOTIFICATION
                    else if( LOCATION_TYPE_FAB_OPTION.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.NEW_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        fabOpnewLotList.add( allnewLots);
                    }
                    else if( LOCATION_TYPE_FAB_OPTION.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.DISCREPENCY_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        fabOpDisLotList.add( allnewLots);
                    }
                    //WAFER STORE RELATED NOTIFICATION LOTS
                    else if( LOCATION_TYPE_SORT.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.NEW_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        sortnewLotList.add( allnewLots );
                    }
                    else if( LOCATION_TYPE_SORT.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.NEW_TRANSIT_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        sortShipLotList.add( allnewLots);
                    }
                    else if( LOCATION_TYPE_SORT.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.DISCREPENCY_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        sortDisLotList.add( allnewLots );
                    }
                    //DIE BANK RELATED NOTIFICATION
                    else if( LOCATION_TYPE_DIE_BANK.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.NEW_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        dbnewLotList.add( allnewLots);
                    }
                    else if( LOCATION_TYPE_DIE_BANK.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.DISCREPENCY_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        dbDisLotList.add( allnewLots);
                    }

                    //ASSEMBLY RELATED NOTIFICATION
                    else if( LOCATION_TYPE_ASSEMBLY.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.NEW_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        assynewLotList.add( allnewLots);
                    }
                    else if( LOCATION_TYPE_ASSEMBLY.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.NEW_TRANSIT_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        assyShipLotList.add( allnewLots);
                    }
                    else if( LOCATION_TYPE_ASSEMBLY.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.DISCREPENCY_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        assyDisLotList.add( allnewLots);
                    }

                    //PRE TEST RELATED NOTIFICATION
                    else if( LOCATION_TYPE_PRE_TEST.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.NEW_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        preTestnewLotList.add( allnewLots);
                    }
                    else if( LOCATION_TYPE_PRE_TEST.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.DISCREPENCY_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        preTestDisLotList.add( allnewLots);
                    }
                    //FT RELATED NOTIFICATION
                    else if( LOCATION_TYPE_FT.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.NEW_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        ftnewLotList.add( allnewLots);
                    }
                    else if( LOCATION_TYPE_FT.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.DISCREPENCY_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        ftDisLotList.add( allnewLots);
                    }
                    //BALL SCAN RELATED NOTIFICATION
                    else if( LOCATION_TYPE_BALLSCAN.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.NEW_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        balScannewLotList.add( allnewLots);
                    }
                    else if( LOCATION_TYPE_BALLSCAN.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.DISCREPENCY_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        balScanDisLotList.add( allnewLots);
                    }
                    //BAKING RELATED NOTIFICATION
                    else if( LOCATION_TYPE_BAKING.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.NEW_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        bakingnewLotList.add( allnewLots);
                    }
                    else if( LOCATION_TYPE_BAKING.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.DISCREPENCY_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                       bakingDisLotList.add( allnewLots);
                    }

                    //FPO RELATED NOTIFICATION
                    else if( LOCATION_TYPE_FPO.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.NEW_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        fponewLotList.add( allnewLots);
                    }
                    else if( LOCATION_TYPE_FPO.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.DISCREPENCY_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        fpoDisLotList.add( allnewLots);
                    }
                    else if( LOCATION_TYPE_TEST.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.NEW_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        testnewLotList.add( allnewLots);
                    }
                    else if( LOCATION_TYPE_TEST.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.DISCREPENCY_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                       testDisLotList.add( allnewLots);
                    }
                     else if( LOCATION_TYPE_TEST.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.NEW_TRANSIT_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                       testShipLotList.add( allnewLots);
                    }//CLASS STORE RELATED NOTIFICATION LOTS
                    else if( LOCATION_TYPE_CLASS_STORE.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.NEW_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        csnewLotList.add( allnewLots );
                    }
                    else if( LOCATION_TYPE_CLASS_STORE.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.DISCREPENCY_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        csDisLotList.add( allnewLots );
                    }
                    else if( LOCATION_TYPE_TAPE_REEL.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.NEW_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        trnewLotList.add( allnewLots );
                    }
                    else if( LOCATION_TYPE_TAPE_REEL.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.DISCREPENCY_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        trDisLotList.add( allnewLots );
                    }
                    else if( LOCATION_TYPE_EOL.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.NEW_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        eolnewLotList.add( allnewLots );
                    }
                    else if( LOCATION_TYPE_EOL.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.DISCREPENCY_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        eolDisLotList.add( allnewLots );
                    }
                    else if( LOCATION_TYPE_EOL.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.NEW_TRANSIT_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                       eolShipLotList.add( allnewLots);
                    }//MARK PACK RELATED NOTIFICATION LOTS
                    else if( LOCATION_TYPE_MARK_AND_PACK.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.NEW_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        markPackNewLotList.add( allnewLots );
                    }
                    else if( LOCATION_TYPE_MARK_AND_PACK.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.DISCREPENCY_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        markPackDisLotList.add( allnewLots );
                    }//FINISHED GOODS RELATED NOTIFICATION
                    else if( ( LOCATION_TYPE_FINISHED_GOODS.equalsIgnoreCase( allnewLots.getProcess() ) || LOCATION_TYPE_FG.equalsIgnoreCase( allnewLots.getProcess() ) ) && Constants.NEW_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        fgnewLotList.add( allnewLots);
                    }
                    else if( ( LOCATION_TYPE_FINISHED_GOODS.equalsIgnoreCase( allnewLots.getProcess() ) || LOCATION_TYPE_FG.equalsIgnoreCase( allnewLots.getProcess() ) ) && Constants.DISCREPENCY_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        fgDisLotList.add( allnewLots );
                    }
                    else if( ( LOCATION_TYPE_FINISHED_GOODS.equalsIgnoreCase( allnewLots.getProcess() ) || LOCATION_TYPE_FG.equalsIgnoreCase( allnewLots.getProcess() ) ) && Constants.NEW_TRANSIT_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        fgShipLotList.add( allnewLots);
                    }
                    else if( LOCATION_TYPE_FINISHED_GOODS.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.ASSEMBLY_LOCATION_VENDOR_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        assemVendorList.add( allnewLots);
                    }
                    else if( LOCATION_TYPE_FT.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.FT_LOW_YIELD_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        ftLowYieldList.add( allnewLots);
                    }
                    else if( LOCATION_TYPE_SORT.equalsIgnoreCase( allnewLots.getProcess() ) && Constants.WS_LOW_YIELD_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
                    {
                        wsLowYieldList.add( allnewLots);
                    }
                }
            }

            // SENDING ALL THE NOTIFICATIONS TO SPECIFIED USERS
           	
            //STEP 1.1 ( FAB NOTIFICATIONS)
            if( fabnewLotList != null && fabnewLotList.size() > 0 && notifyNewLots )
            {
                boolean isAlertSent =  notifyUserAboutNewLots( fabnewLotList,  vendorName ,LOCATION_TYPE_FAB , Constants.NEW_LOT_NOTIFICATION  );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName ,LOCATION_TYPE_FAB, Constants.NEW_LOT_NOTIFICATION );
                }
            }

            if ( fabDisLotList != null && fabDisLotList.size() > 0 && notifyDisLots )
            {
               boolean isAlertSent = notifyUserAboutDiscrepancyLots( fabDisLotList ,vendorName, LOCATION_TYPE_FAB, Constants.DISCREPENCY_LOT_NOTIFICATION );
               //If Notification sent successfully , updating the Notification sent details
               if ( isAlertSent )
               {
                  NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_FAB, Constants.DISCREPENCY_LOT_NOTIFICATION );
               }
            }
            if ( fabTransitLotList != null && fabTransitLotList.size() > 0 && notifyNewShipLots  )
            {
               boolean isAlertSent = notifyUserAboutNewTransitLots( fabTransitLotList ,vendorName, LOCATION_TYPE_FAB, Constants.NEW_TRANSIT_LOT_NOTIFICATION );
               //If Notification sent successfully , updating the Notification sent details
               if ( isAlertSent )
               {
                  NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_FAB, Constants.NEW_TRANSIT_LOT_NOTIFICATION );
               }
            }

            if ( noPOfabLotList != null && noPOfabLotList.size() > 0 )
            {
               boolean isAlertSent = notifyUserAboutNoPOLots( noPOfabLotList ,vendorName, LOCATION_TYPE_FAB, Constants.NO_PO_LOT_NOTIFICATION );
               //If Notification sent successfully , updating the Notification sent details
               if ( isAlertSent )
               {
                  NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_FAB, Constants.NO_PO_LOT_NOTIFICATION );
               }
            }
            //STEP 1.2 ( WAFER STORE NOTIFICATIONS)
            if( wbnewLotList != null && wbnewLotList.size() > 0 && notifyNewLots  )
            {
                boolean isAlertSent = notifyUserAboutNewLots( wbnewLotList, vendorName ,LOCATION_TYPE_WAFER_STORE , Constants.NEW_LOT_NOTIFICATION  );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_WAFER_STORE, Constants.NEW_LOT_NOTIFICATION );
                }
            }

            if( wbDisLotList != null && wbDisLotList.size() > 0 && notifyDisLots  )
            {
                boolean isAlertSent = notifyUserAboutDiscrepancyLots( wbDisLotList,  vendorName, LOCATION_TYPE_WAFER_STORE, Constants.DISCREPENCY_LOT_NOTIFICATION );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                   NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_WAFER_STORE, Constants.DISCREPENCY_LOT_NOTIFICATION );
                }
            }                                                
           
            //STEP 1.3 ( SORT NOTIFICATIONS)
            if( sortnewLotList != null && sortnewLotList.size() > 0 && notifyNewLots  )
            {
                boolean isAlertSent = notifyUserAboutNewLots( sortnewLotList, vendorName ,LOCATION_TYPE_SORT , Constants.NEW_LOT_NOTIFICATION  );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_SORT, Constants.NEW_LOT_NOTIFICATION );
                }
            }

            if( sortDisLotList != null && sortDisLotList.size() > 0 && notifyDisLots  )
            {
                boolean isAlertSent = notifyUserAboutDiscrepancyLots( sortDisLotList,  vendorName, LOCATION_TYPE_SORT, Constants.DISCREPENCY_LOT_NOTIFICATION );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_SORT, Constants.DISCREPENCY_LOT_NOTIFICATION );
                }
            }
            if ( sortShipLotList != null && sortShipLotList.size() > 0 && notifyNewShipLots  )
            {
               boolean isAlertSent = notifyUserAboutNewTransitLots( sortShipLotList ,vendorName, LOCATION_TYPE_SORT, Constants.NEW_TRANSIT_LOT_NOTIFICATION );
               //If Notification sent successfully , updating the Notification sent details
               if ( isAlertSent )
               {
                  NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_SORT, Constants.NEW_TRANSIT_LOT_NOTIFICATION );
               }
            }
            //STEP 1.4 ( FAB OPTION NOTIFICATIONS)
            if( fabOpnewLotList != null && fabOpnewLotList.size() > 0 && notifyNewLots  )
            {
                boolean isAlertSent = notifyUserAboutNewLots( fabOpnewLotList, vendorName ,LOCATION_TYPE_FAB_OPTION , Constants.NEW_LOT_NOTIFICATION  );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_FAB_OPTION, Constants.NEW_LOT_NOTIFICATION );
                }
            }

            if( fabOpDisLotList != null && fabOpDisLotList.size() > 0 && notifyDisLots  )
            {
                boolean isAlertSent = notifyUserAboutDiscrepancyLots( fabOpDisLotList,  vendorName, LOCATION_TYPE_FAB_OPTION, Constants.DISCREPENCY_LOT_NOTIFICATION );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_FAB_OPTION, Constants.DISCREPENCY_LOT_NOTIFICATION );
                }
            }

            //STEP 1.5 ( DIEBANK STORE NOTIFICATIONS )
            if( dbnewLotList != null && dbnewLotList.size() > 0 && notifyNewLots   )
            {
                boolean isAlertSent =  notifyUserAboutNewLots( dbnewLotList,  vendorName ,LOCATION_TYPE_DIE_BANK , Constants.NEW_LOT_NOTIFICATION  );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName ,LOCATION_TYPE_DIE_BANK, Constants.NEW_LOT_NOTIFICATION );
                }
            }

            if( dbDisLotList != null && dbDisLotList.size() > 0 && notifyDisLots  )
            {
                boolean isAlertSent = notifyUserAboutDiscrepancyLots( dbDisLotList , vendorName, LOCATION_TYPE_DIE_BANK, Constants.DISCREPENCY_LOT_NOTIFICATION );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName ,LOCATION_TYPE_DIE_BANK, Constants.DISCREPENCY_LOT_NOTIFICATION );
                }
            }

            //STEP 1.6 ( ASSEMBLY NOTIFICATIONS )
            if( assynewLotList != null && assynewLotList.size() > 0 && notifyNewLots  )
            {
                boolean isAlertSent =  notifyUserAboutNewLots( assynewLotList, vendorName ,LOCATION_TYPE_ASSEMBLY , Constants.NEW_LOT_NOTIFICATION  );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName ,LOCATION_TYPE_ASSEMBLY, Constants.NEW_LOT_NOTIFICATION );
                }
            }
            if ( assyShipLotList != null && assyShipLotList.size() > 0 && notifyNewShipLots  )
            {
               boolean isAlertSent = notifyUserAboutNewTransitLots( assyShipLotList ,vendorName, LOCATION_TYPE_ASSEMBLY, Constants.NEW_TRANSIT_LOT_NOTIFICATION );
               //If Notification sent successfully , updating the Notification sent details
               if ( isAlertSent )
               {
                  NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_ASSEMBLY, Constants.NEW_TRANSIT_LOT_NOTIFICATION );
               }
            }
           if ( assyDisLotList != null && assyDisLotList.size() > 0 && notifyDisLots )
           {
                boolean isAlertSent = notifyUserAboutDiscrepancyLots( assyDisLotList ,vendorName, LOCATION_TYPE_ASSEMBLY, Constants.DISCREPENCY_LOT_NOTIFICATION );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_ASSEMBLY, Constants.DISCREPENCY_LOT_NOTIFICATION );
                }
           }
           //PRE TEST
            if( preTestnewLotList != null && preTestnewLotList.size() > 0 && notifyNewLots  )
            {
                boolean isAlertSent =  notifyUserAboutNewLots( preTestnewLotList, vendorName , LOCATION_TYPE_PRE_TEST , Constants.NEW_LOT_NOTIFICATION  );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName ,LOCATION_TYPE_PRE_TEST, Constants.NEW_LOT_NOTIFICATION );
                }
            }

           if ( preTestDisLotList != null && preTestDisLotList.size() > 0 && notifyDisLots  )
           {
                boolean isAlertSent = notifyUserAboutDiscrepancyLots( preTestDisLotList ,vendorName, LOCATION_TYPE_PRE_TEST, Constants.DISCREPENCY_LOT_NOTIFICATION );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_PRE_TEST, Constants.DISCREPENCY_LOT_NOTIFICATION );
                }
           }
            //STEP 1.7 ( FT NOTIFICATIONS )
            if( ftnewLotList != null && ftnewLotList.size() > 0 && notifyNewLots  )
            {
                boolean isAlertSent =  notifyUserAboutNewLots( ftnewLotList, vendorName , LOCATION_TYPE_FT , Constants.NEW_LOT_NOTIFICATION  );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName ,LOCATION_TYPE_FT, Constants.NEW_LOT_NOTIFICATION );
                }
            }

           if ( ftDisLotList != null && ftDisLotList.size() > 0 && notifyDisLots  )
           {
                boolean isAlertSent = notifyUserAboutDiscrepancyLots( ftDisLotList ,vendorName, LOCATION_TYPE_FT, Constants.DISCREPENCY_LOT_NOTIFICATION );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_FT, Constants.DISCREPENCY_LOT_NOTIFICATION );
                }
           }
            //STEP 1.8 ( BALL SCAN NOTIFICATIONS )
            if( balScannewLotList != null && balScannewLotList.size() > 0 && notifyNewLots  )
            {
                boolean isAlertSent =  notifyUserAboutNewLots( balScannewLotList, vendorName , LOCATION_TYPE_BALLSCAN , Constants.NEW_LOT_NOTIFICATION  );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName ,LOCATION_TYPE_BALLSCAN, Constants.NEW_LOT_NOTIFICATION );
                }
            }

           if ( balScanDisLotList != null && balScanDisLotList.size() > 0 && notifyDisLots  )
           {
                boolean isAlertSent = notifyUserAboutDiscrepancyLots( balScanDisLotList ,vendorName, LOCATION_TYPE_BALLSCAN, Constants.DISCREPENCY_LOT_NOTIFICATION );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_BALLSCAN, Constants.DISCREPENCY_LOT_NOTIFICATION );
                }
           }
            //STEP 1.9 ( BAKING NOTIFICATIONS )
            if( bakingnewLotList != null && bakingnewLotList.size() > 0 && notifyNewLots  )
            {
                boolean isAlertSent =  notifyUserAboutNewLots( bakingnewLotList, vendorName , LOCATION_TYPE_BAKING , Constants.NEW_LOT_NOTIFICATION  );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName ,LOCATION_TYPE_BAKING, Constants.NEW_LOT_NOTIFICATION );
                }
            }

           if ( bakingDisLotList != null && bakingDisLotList.size() > 0 && notifyDisLots  )
           {
                boolean isAlertSent = notifyUserAboutDiscrepancyLots( bakingDisLotList ,vendorName, LOCATION_TYPE_BAKING, Constants.DISCREPENCY_LOT_NOTIFICATION );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_BAKING, Constants.DISCREPENCY_LOT_NOTIFICATION );
                }
           }

           //STEP 1.10 ( FPO NOTIFICATIONS )
           if( fponewLotList != null && fponewLotList.size() > 0 && notifyNewLots  )
           {
               boolean isAlertSent =  notifyUserAboutNewLots( fponewLotList, vendorName , LOCATION_TYPE_FPO , Constants.NEW_LOT_NOTIFICATION  );
               //If Notification sent successfully , updating the Notification sent details
               if ( isAlertSent )
               {
                   NewAdaptorDAO.updateUserNotifiedDetails( vendorName ,LOCATION_TYPE_FPO, Constants.NEW_LOT_NOTIFICATION );
               }
           }

            if (fpoDisLotList != null && fpoDisLotList.size() > 0 && notifyDisLots  )
            {
                boolean isAlertSent = notifyUserAboutDiscrepancyLots(fpoDisLotList, vendorName, LOCATION_TYPE_FPO, Constants.DISCREPENCY_LOT_NOTIFICATION);
                //If Notification sent successfully , updating the Notification sent details
                if (isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails(vendorName, LOCATION_TYPE_FPO, Constants.DISCREPENCY_LOT_NOTIFICATION);
                }
            }

            //STEP 1.11 ( TEST NOTIFICATIONS )
            if (testnewLotList != null && testnewLotList.size() > 0 && notifyNewLots  )
            {
                boolean isAlertSent = notifyUserAboutNewLots(testnewLotList, vendorName, LOCATION_TYPE_TEST, Constants.NEW_LOT_NOTIFICATION);
                //If Notification sent successfully , updating the Notification sent details
                if (isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails(vendorName, LOCATION_TYPE_TEST, Constants.NEW_LOT_NOTIFICATION);
                }
            }

            if (testDisLotList != null && testDisLotList.size() > 0 && notifyDisLots )
            {
                boolean isAlertSent = notifyUserAboutDiscrepancyLots(testDisLotList, vendorName, LOCATION_TYPE_TEST, Constants.DISCREPENCY_LOT_NOTIFICATION);
                //If Notification sent successfully , updating the Notification sent details
                if (isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails(vendorName, LOCATION_TYPE_TEST, Constants.DISCREPENCY_LOT_NOTIFICATION);
                }
            }
            if ( testShipLotList != null && testShipLotList.size() > 0 && notifyNewShipLots )
            {
                boolean isAlertSent = notifyUserAboutNewTransitLots( testShipLotList, vendorName, LOCATION_TYPE_TEST, Constants.NEW_TRANSIT_LOT_NOTIFICATION);
                //If Notification sent successfully , updating the Notification sent details
                if (isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails(vendorName, LOCATION_TYPE_TEST, Constants.NEW_TRANSIT_LOT_NOTIFICATION );
                }
            }

            //STEP 1.12 ( CLASS STORE NOTIFICATIONS)
            if( csnewLotList != null && csnewLotList.size() > 0 && notifyNewLots  )
            {
                boolean isAlertSent = notifyUserAboutNewLots( csnewLotList, vendorName ,LOCATION_TYPE_CLASS_STORE , Constants.NEW_LOT_NOTIFICATION  );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_CLASS_STORE, Constants.NEW_LOT_NOTIFICATION );
                }
            }

            if( csDisLotList != null && csDisLotList.size() > 0 && notifyDisLots  )
            {
                boolean isAlertSent = notifyUserAboutDiscrepancyLots( csDisLotList,  vendorName, LOCATION_TYPE_CLASS_STORE, Constants.DISCREPENCY_LOT_NOTIFICATION );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_CLASS_STORE, Constants.DISCREPENCY_LOT_NOTIFICATION );
                }
            }
            if( trnewLotList != null && trnewLotList.size() > 0 && notifyNewLots  )
            {
                boolean isAlertSent = notifyUserAboutNewLots( trnewLotList, vendorName ,LOCATION_TYPE_TAPE_REEL , Constants.NEW_LOT_NOTIFICATION  );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_TAPE_REEL, Constants.NEW_LOT_NOTIFICATION );
                }
            }

            if( trDisLotList != null && trDisLotList.size() > 0 && notifyDisLots  )
            {
                boolean isAlertSent = notifyUserAboutDiscrepancyLots(trDisLotList,  vendorName, LOCATION_TYPE_TAPE_REEL, Constants.DISCREPENCY_LOT_NOTIFICATION );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_TAPE_REEL, Constants.DISCREPENCY_LOT_NOTIFICATION );
                }
            }
            if( eolnewLotList != null && eolnewLotList.size() > 0 && notifyNewLots  )
            {
                boolean isAlertSent = notifyUserAboutNewLots( eolnewLotList, vendorName ,LOCATION_TYPE_EOL , Constants.NEW_LOT_NOTIFICATION  );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_EOL, Constants.NEW_LOT_NOTIFICATION );
                }
            }

            if( eolDisLotList != null && eolDisLotList.size() > 0 && notifyDisLots  )
            {
                boolean isAlertSent = notifyUserAboutDiscrepancyLots( eolDisLotList,  vendorName, LOCATION_TYPE_EOL, Constants.DISCREPENCY_LOT_NOTIFICATION );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_EOL, Constants.DISCREPENCY_LOT_NOTIFICATION );
                }
            }
            if ( eolShipLotList != null && eolShipLotList.size() > 0 && notifyNewShipLots )
            {
                boolean isAlertSent = notifyUserAboutNewTransitLots( eolShipLotList, vendorName, LOCATION_TYPE_EOL, Constants.NEW_TRANSIT_LOT_NOTIFICATION);
                //If Notification sent successfully , updating the Notification sent details
                if (isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails(vendorName, LOCATION_TYPE_EOL, Constants.NEW_TRANSIT_LOT_NOTIFICATION );
                }
            }
             //STEP 1.13 ( MARK & PACK NOTIFICATIONS)
            if( markPackNewLotList != null && markPackNewLotList.size() > 0 && notifyNewLots  )
            {
                boolean isAlertSent = notifyUserAboutNewLots( markPackNewLotList, vendorName ,LOCATION_TYPE_MARK_AND_PACK , Constants.NEW_LOT_NOTIFICATION  );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_MARK_AND_PACK, Constants.NEW_LOT_NOTIFICATION );
                }
            }

            if( markPackDisLotList != null && markPackDisLotList.size() > 0 && notifyDisLots  )
            {
                boolean isAlertSent = notifyUserAboutDiscrepancyLots( markPackDisLotList,  vendorName, LOCATION_TYPE_MARK_AND_PACK, Constants.DISCREPENCY_LOT_NOTIFICATION );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_MARK_AND_PACK, Constants.DISCREPENCY_LOT_NOTIFICATION );
                }
            }

           //STEP 1.13 ( FINISHED GOODS NOTIFICATIONS )
           if( fgnewLotList != null && fgnewLotList.size() > 0 && notifyNewLots  )
           {
                boolean isAlertSent =  notifyUserAboutNewLots( fgnewLotList,  vendorName, LOCATION_TYPE_FINISHED_GOODS ,Constants.NEW_LOT_NOTIFICATION   );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName ,LOCATION_TYPE_FINISHED_GOODS, Constants.NEW_LOT_NOTIFICATION );
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName ,LOCATION_TYPE_FG, Constants.NEW_LOT_NOTIFICATION );
                }
           }
            if ( fgShipLotList != null && fgShipLotList.size() > 0 && notifyNewShipLots  )
            {
               boolean isAlertSent = notifyUserAboutNewTransitLots( fgShipLotList ,vendorName, LOCATION_TYPE_FINISHED_GOODS, Constants.NEW_TRANSIT_LOT_NOTIFICATION );
               //If Notification sent successfully , updating the Notification sent details
               if ( isAlertSent )
               {
                  NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_FINISHED_GOODS, Constants.NEW_TRANSIT_LOT_NOTIFICATION );
                  NewAdaptorDAO.updateUserNotifiedDetails( vendorName , LOCATION_TYPE_FG, Constants.NEW_TRANSIT_LOT_NOTIFICATION );
               }
            }

           if( fgDisLotList != null && fgDisLotList.size() > 0 && notifyDisLots )
           {
                boolean isAlertSent = notifyUserAboutDiscrepancyLots( fgDisLotList , vendorName, LOCATION_TYPE_FINISHED_GOODS,Constants.DISCREPENCY_LOT_NOTIFICATION );

                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName ,LOCATION_TYPE_FINISHED_GOODS, Constants.DISCREPENCY_LOT_NOTIFICATION );
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName ,LOCATION_TYPE_FG, Constants.DISCREPENCY_LOT_NOTIFICATION );
                }
           }

           if( assemVendorList != null && assemVendorList.size() > 0 )
           {
                boolean isAlertSent = notifyUserAboutNoAssemblyVendorLots( assemVendorList , vendorName, LOCATION_TYPE_FINISHED_GOODS,Constants.ASSEMBLY_LOCATION_VENDOR_NOTIFICATION );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent  )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName ,LOCATION_TYPE_FINISHED_GOODS, Constants.ASSEMBLY_LOCATION_VENDOR_NOTIFICATION );
                }
           }

            if( ftLowYieldList != null && ftLowYieldList.size() > 0 )
           {
                boolean isAlertSent = notifyUserAboutLowYieldLots( ftLowYieldList , vendorName, LOCATION_TYPE_FT, Constants.FT_LOW_YIELD_LOT_NOTIFICATION );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent  )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName ,LOCATION_TYPE_TEST, Constants.FT_LOW_YIELD_LOT_NOTIFICATION );
                }
           }
           if( wsLowYieldList != null && wsLowYieldList.size() > 0 )
           {
                boolean isAlertSent = notifyUserAboutLowYieldLots( wsLowYieldList , vendorName, LOCATION_TYPE_SORT,Constants.WS_LOW_YIELD_LOT_NOTIFICATION );
                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent  )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName ,LOCATION_TYPE_SORT, Constants.WS_LOW_YIELD_LOT_NOTIFICATION );
                }
           }
           /*if ( dropOutLotList != null && dropOutLotList.size() > 0 )
           {                  
                boolean isAlertSent = notifyUserAboutDropOutLots( dropOutLotList , vendorName, Constants.DROPOUT_NOTIFICATION );

                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent  )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( vendorName, null, Constants.DROPOUT_NOTIFICATION );
                }
           }*/

        }
        catch ( Exception e )
        {
            log( "ERROR!! ERROR!! ERROR!! Could not Send the Alert Emails : " + e );
            e.printStackTrace();
        }
    }
   	
   	public static void sendAllNotificationsIkanos(String vendorName, boolean notifyNewLots, boolean notifyNewShipLots, boolean notifyDisLots ) throws Exception
	{
		try
		{
			FabStage allnewLots         = null;
			ArrayList<FabStage> allNSLotsList     = getAllNotificationLotsList( vendorName );

			ArrayList<FabStage> newLotList     = new ArrayList<FabStage>();
			ArrayList<FabStage> disLotList     = new ArrayList<FabStage>();
			ArrayList<FabStage> transitLotList = new ArrayList<FabStage>();

			////GETTING ALL THE  PROCESS NOTIFICATION TYPE LOTS
			if( allNSLotsList != null && allNSLotsList.size() > 0 )
			{
				for( int i=0; i < allNSLotsList.size(); i++)
				{
					allnewLots = (FabStage)allNSLotsList.get(i);

					//FAB RELATED NOTIFICATION LOTS
					if( Constants.NEW_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
					{
						newLotList.add( allnewLots );
					}
					else if( Constants.DISCREPENCY_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
					{
						disLotList.add( allnewLots);
					}
					else if( Constants.NEW_TRANSIT_LOT_NOTIFICATION.equalsIgnoreCase( allnewLots.getNotificationName() ) )
					{
						transitLotList.add( allnewLots);
					}
				}
			}

					
			// SENDING ALL THE NOTIFICATIONS TO SPECIFIED USERS

			//STEP 1.1 ( FAB NOTIFICATIONS)
			if( newLotList != null && newLotList.size() > 0 && notifyNewLots )
			{
				boolean isAlertSent =  notifyUserAboutNewLots( newLotList,  vendorName, null , Constants.NEW_LOT_NOTIFICATION  );
				//If Notification sent successfully , updating the Notification sent details
				if ( isAlertSent )
				{
					NewAdaptorDAO.updateUserNotifiedDetails( vendorName, null, Constants.NEW_LOT_NOTIFICATION );
				}
			}

			if ( disLotList != null && disLotList.size() > 0 && notifyDisLots )
			{
				boolean isAlertSent = notifyUserAboutDiscrepancyLots( disLotList ,vendorName, null, Constants.DISCREPENCY_LOT_NOTIFICATION );
				//If Notification sent successfully , updating the Notification sent details
				if ( isAlertSent )
				{
					NewAdaptorDAO.updateUserNotifiedDetails( vendorName , null, Constants.DISCREPENCY_LOT_NOTIFICATION );
				}
			}

			if ( transitLotList != null && transitLotList.size() > 0 && notifyNewShipLots  )
			{
				boolean isAlertSent = notifyUserAboutNewTransitLots( transitLotList ,vendorName, null, Constants.NEW_TRANSIT_LOT_NOTIFICATION );
				//If Notification sent successfully , updating the Notification sent details
				if ( isAlertSent )
				{
					NewAdaptorDAO.updateUserNotifiedDetails( vendorName , null, Constants.NEW_TRANSIT_LOT_NOTIFICATION );
				}
			}

		}
		catch ( Exception e )
		{
			log( "ERROR!! ERROR!! ERROR!! Could not Send the Alert Emails : " + e );
			e.printStackTrace();
		}
	}
   	
   public static void sendAllNotificationsByNsType(String nsType) throws Exception
   {
        FabStage allDropuOutLots = null;
        ArrayList<FabStage> dropOutLotList = new ArrayList<FabStage>();
        try
        {
            ArrayList<FabStage> allNSLotsList = getAllNotificationLotsListByNsType( nsType, false );

            if( allNSLotsList != null && allNSLotsList.size() > 0 )
            {
                for( int i=0; i < allNSLotsList.size(); i++)
                {
                    allDropuOutLots = (FabStage)allNSLotsList.get(i);

                    //DROP OUT LOTS NOTIFICATION
                    if ( Constants.DROPOUT_NOTIFICATION.equalsIgnoreCase( allDropuOutLots.getNotificationName() ) )
                    {
                        dropOutLotList.add( allDropuOutLots );
                    }
                }
            }

            if ( dropOutLotList != null && dropOutLotList.size() > 0 )
            {
                boolean isAlertSent = notifyUserAboutDropOutLots( dropOutLotList , Constants.DROPOUT_NOTIFICATION, null );

                //If Notification sent successfully , updating the Notification sent details
                if ( isAlertSent  )
                {
                    NewAdaptorDAO.updateUserNotifiedDetails( null, null, Constants.DROPOUT_NOTIFICATION );
                }
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
   }
   public static void sendAllNotificationsByNsType(String nsType, String toEmailIds) throws Exception
   {
        try
        {
            if ( Constants.DROPOUT_NOTIFICATION.equalsIgnoreCase(nsType) )
            {
                AdaptorDAO.updateDropOutLotsNotificationStatus(null);
            }
            ArrayList<FabStage> allNSLotsList = getAllNotificationLotsListByNsType( nsType, true );

            if ( Constants.DROPOUT_NOTIFICATION.equalsIgnoreCase(nsType) )
            {
                notifyUserAboutDropOutLots( allNSLotsList , Constants.DROPOUT_NOTIFICATION, toEmailIds );
            }
            if ( Constants.DISCREPENCY_LOT_NOTIFICATION.equalsIgnoreCase(nsType) )
            {
                 notifyUserAboutDiscrepancyLots( allNSLotsList , toEmailIds );
            }
            if ( Constants.NEW_TRANSIT_LOT_NOTIFICATION.equalsIgnoreCase(nsType) )
            {
                 notifyUserAboutNewTransitLots( allNSLotsList , toEmailIds );
            }
            if ( Constants.NEW_LOT_NOTIFICATION.equalsIgnoreCase(nsType) )
            {
                 notifyUserAboutNewLots( allNSLotsList , toEmailIds );
            }
            if ( Constants.FT_LOW_YIELD_LOT_NOTIFICATION.equalsIgnoreCase(nsType) )
            {
                 notifyUserAboutLowYieldLots( allNSLotsList , toEmailIds, "FT" );
            }
            if ( Constants.WS_LOW_YIELD_LOT_NOTIFICATION.equalsIgnoreCase(nsType) )
            {
                 notifyUserAboutLowYieldLots( allNSLotsList , toEmailIds , "WS");
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
   }
    public static void updateDropOutLotsNotificationStatus(DBTransaction dbTransaction) throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement(" { CALL SP_UPDATE_DROPOUT_NOTIFICATION_LOTS() }");

            pstmt.execute();
            if (dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            System.out.println("Exception While updateing the Drop Out lot Notification status");
        }

        finally
        {
          if (dbTransaction == null && !success && conn != null)
          {
              try
              {
                conn.rollback();
              }
              catch(Exception e)
              {
                  
              }
          }
        }
    }
    /**
     *
     * @param newLotsList
     * @param vendor
     * @param process
     * @param nsType  Notifcation Name
     * @return boolean
     * @throws Exception ee
     */
    public static boolean notifyUserAboutNewLots( ArrayList<FabStage> newLotsList, String vendor, String process,String nsType ) throws Exception
    {
        if ( newLotsList == null || newLotsList.size() <= 0 )
        {
            return false;
        }

        FabStage fabStage   = null;
        boolean success     = false;
        String[] emailArray = null;

        if ( LOCATION_TYPE_WAFER_BANK.equalsIgnoreCase( process ) )
        {
             emailArray = NotificationDAO.loadEmailsByNSType( vendor, LOCATION_TYPE_WAFER_STORE, nsType );
        }
        else if ( LOCATION_TYPE_FAB_OPTION.equalsIgnoreCase( process ) )
        {
             emailArray = NotificationDAO.loadEmailsByNSType( vendor, LOCATION_TYPE_FAB, nsType );
        }
        else if( LOCATION_TYPE_FT.equalsIgnoreCase( process )  || LOCATION_TYPE_BALLSCAN.equalsIgnoreCase( process )
                || LOCATION_TYPE_BAKING.equalsIgnoreCase( process ) ||  LOCATION_TYPE_FPO.equalsIgnoreCase( process )
                )
        {
              emailArray = NotificationDAO.loadEmailsByNSType( vendor, WIP_TYPE_TEST, nsType );
        }
        else if( LOCATION_TYPE_TAPE_REEL.equalsIgnoreCase( process ) || LOCATION_TYPE_EOL.equalsIgnoreCase( process )
                || LOCATION_TYPE_PRE_TEST.equalsIgnoreCase( process ) )
        {
              emailArray = NotificationDAO.loadEmailsByNSType( vendor, LOCATION_TYPE_CLASS_STORE, nsType );
        }
        else if( LOCATION_TYPE_MARK_AND_PACK.equalsIgnoreCase( process ) )
        {
              emailArray = NotificationDAO.loadEmailsByNSType( vendor, "Mark &amp; Pack", nsType );
        }
        else
        {
             emailArray = NotificationDAO.loadEmailsByNSType( vendor, process, nsType );
        }

        String from = ConfigManager.getScalarAdminEmail();
        int index = from.lastIndexOf("\\");
        from  = from.substring( index+1, from.length() );
        String to = null;
        if ( emailArray != null && emailArray.length > 0 )
        {
            to   = StringUtils.concatStrings ( emailArray, Mailer.EMAIL_SEPARATOR );
        }
        else
        {
            log("New lots Notification not set up for users");
            return success;
        }
        String subject = "Lot Moved to " + vendor.toUpperCase()+"  "+ process.toUpperCase();

        // FORMAT MESSAGE
        StringBuffer buf = new StringBuffer( 2098 );
        buf.append( "<HTML><HEAD><META http-equiv=Content-Type content='text/html; charset=BIG5'></HEAD><BODY>" );
        buf.append( "<TABLE ALIGN = 'CENTER' style='FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Verdana' borderColor='#ffffff' cellSpacing='1' cellPadding='3' width='50%' border=0>" );
        buf.append( "<TR bgColor='#ccccff'><TD colspan='8' width='100%' align='center' ><B>NEW LOTS ARE ADDED TO THE CHIPTRAC SYSTEM <B></TD>" );
        buf.append( "<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'><B>SL NO.<B></TD><TD width='10%' align='CENTER'><B>CUSTOMER PRODUCT<B></TD>");
        buf.append( "<TD width='8%' align='CENTER'><B>LOT NUMBER<B></TD><TD width='8%' align='CENTER'><B>FAB LOT NUMBER<B></TD><TD width='8%' align='CENTER'><B>WAFER QTY<B></TD>");
        buf.append( "<TD width='8%' align='CENTER'><B>DIE QTY<B></TD><TD width='8%' align='CENTER'><B>PROCESS<B></TD><TD width='6%' align='CENTER'><B>VENDOR<B></TD></TR>" );

        for ( int i = 0; i < newLotsList.size(); i++ )
        {
            fabStage = ( FabStage ) newLotsList.get( i );
            buf.append("<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'>").append(i + 1).append(".");
            buf.append("</TD><TD width='10%' align='CENTER'>").append(fabStage.getDeviceName()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getLotNumber()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getFabLotNumber()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getWaferQtyIn() == null ? "":fabStage.getWaferQtyIn() ).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getQtyIn()== null ? "":fabStage.getQtyIn() ).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getProcess()).append("</TD>");
            buf.append("<TD width='6%' align='CENTER'>").append(fabStage.getVendorName()).append("</TD></TR>");
        }
        buf.append( "</TR></TABLE></BODY></HTML>\n" );
        String message = buf.toString();

        try
        {
            // SEND MESSAGE
            Mailer.send ( from, to, null, null, subject, message, null, Mailer.CONTENT_TYPE_HTML, false );
            success = true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            success = false;
            log("Exception while sending new Lot mail notifaction");
        }

        return success;
    }
    public static void notifyUserAboutNewLots( ArrayList<FabStage> newLotsList, String toEmailIds ) throws Exception
    {
        if ( newLotsList == null || newLotsList.size() <= 0 )
        {
            return;
        }
        FabStage fabStage   = null;

        String from = ConfigManager.getScalarAdminEmail();
        int index = from.lastIndexOf("\\");
        from  = from.substring( index+1, from.length() );

        if ( toEmailIds == null || toEmailIds.length() == 0 )
        {
            log("New lots Notification not set up for users");
            return;
        }

        String subject = "New lots created between " + DateUtil.getPreviousDate( 1, "MM/dd/yyyy HH:mm") + " and " +
                         DateUtil.getCurrentDate("MM/dd/yyyy HH:mm");

        // FORMAT MESSAGE
        StringBuffer buf = new StringBuffer( 2098 );
        buf.append( "<HTML><HEAD><META http-equiv=Content-Type content='text/html; charset=BIG5'></HEAD><BODY>" );
        buf.append( "<TABLE ALIGN = 'CENTER' style='FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Verdana' borderColor='#ffffff' cellSpacing='1' cellPadding='3' width='50%' border=0>" );
        buf.append( "<TR bgColor='#ccccff'><TD colspan='8' width='100%' align='center' ><B>NEW LOTS ARE ADDED TO THE CHIPTRAC SYSTEM <B></TD>" );
        buf.append( "<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'><B>SL NO.<B></TD><TD width='10%' align='CENTER'><B>CUSTOMER PRODUCT<B></TD>");
        buf.append( "<TD width='8%' align='CENTER'><B>LOT NUMBER<B></TD><TD width='8%' align='CENTER'><B>FAB LOT NUMBER<B></TD><TD width='8%' align='CENTER'><B>WAFER QTY<B></TD>");
        buf.append( "<TD width='8%' align='CENTER'><B>DIE QTY<B></TD><TD width='8%' align='CENTER'><B>PROCESS<B></TD><TD width='6%' align='CENTER'><B>VENDOR<B></TD></TR>" );

        for ( int i = 0; i < newLotsList.size(); i++ )
        {
            fabStage = ( FabStage ) newLotsList.get( i );
            buf.append("<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'>").append(i + 1).append(".");
            buf.append("</TD><TD width='10%' align='CENTER'>").append(fabStage.getDeviceName()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getLotNumber()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getFabLotNumber()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getWaferQtyIn() == null ? "":fabStage.getWaferQtyIn() ).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getQtyIn()== null ? "":fabStage.getQtyIn() ).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getProcess()).append("</TD>");
            buf.append("<TD width='6%' align='CENTER'>").append(fabStage.getVendorName()).append("</TD></TR>");
        }
        buf.append( "</TR></TABLE></BODY></HTML>\n" );
        String message = buf.toString();

        try
        {
            // SEND MESSAGE
            Mailer.send ( from, toEmailIds, null, null, subject, message, null, Mailer.CONTENT_TYPE_HTML, false );
        }
        catch(Exception e)
        {
            //e.printStackTrace();
            log("Exception while sending new Lot mail notifaction as of " + DateUtil.getCurrentDate());
        }
    }

    /**
     * To send the new Transit Lots mail to user
     * @param newShipLotsList
     * @throws Exception
     */
    public static boolean notifyUserAboutNewTransitLots( ArrayList<FabStage> newShipLotsList, String vendor, String process, String nsType  ) throws Exception
    {
        if ( newShipLotsList == null || newShipLotsList.size() <= 0 )
        {
            return false;
        }
        FabStage fabStage   = null;
        boolean success     = false;
        String[]  emailArray = null;

        if ( LOCATION_TYPE_WAFER_BANK.equalsIgnoreCase( process ) )
        {
             emailArray = NotificationDAO.loadEmailsByNSType( vendor, LOCATION_TYPE_WAFER_STORE, nsType );
        }
        else if ( LOCATION_TYPE_FAB_OPTION.equalsIgnoreCase( process ) )
        {
             emailArray = NotificationDAO.loadEmailsByNSType( vendor, LOCATION_TYPE_FAB, nsType );
        }
        else if( LOCATION_TYPE_FT.equalsIgnoreCase( process )  || LOCATION_TYPE_BALLSCAN.equalsIgnoreCase( process )
                || LOCATION_TYPE_BAKING.equalsIgnoreCase( process ) ||  LOCATION_TYPE_FPO.equalsIgnoreCase( process )
                )
        {
              emailArray = NotificationDAO.loadEmailsByNSType( vendor, WIP_TYPE_TEST, nsType );
        }
        else if( LOCATION_TYPE_TAPE_REEL.equalsIgnoreCase( process ) || LOCATION_TYPE_EOL.equalsIgnoreCase( process )
                || LOCATION_TYPE_PRE_TEST.equalsIgnoreCase( process ) )
        {
              emailArray = NotificationDAO.loadEmailsByNSType( vendor, LOCATION_TYPE_CLASS_STORE, nsType );
        }
        else if( LOCATION_TYPE_MARK_AND_PACK.equalsIgnoreCase( process ) )
        {
              emailArray = NotificationDAO.loadEmailsByNSType( vendor, "Mark &amp; Pack", nsType );
        }
        else
        {
             emailArray = NotificationDAO.loadEmailsByNSType( vendor, process, nsType );
        }

        String from = ConfigManager.getScalarAdminEmail();

        int index = from.lastIndexOf("\\");
        from  = from.substring( index+1, from.length() );
        String to = null;
        
        if ( emailArray != null && emailArray.length > 0 )
        {
            to   = StringUtils.concatStrings ( emailArray, Mailer.EMAIL_SEPARATOR );
        }
        else
        {
            log("Transit lots Notification not set up for users");
            return success;
        }

        // FORMAT MESSAGE
        StringBuffer buf = new StringBuffer( 2098 );
        buf.append( "<HTML><HEAD><META http-equiv=Content-Type content='text/html; charset=BIG5'></HEAD><BODY>" );
        buf.append( "<TABLE ALIGN = 'CENTER' style='FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Verdana' borderColor='#ffffff' cellSpacing='1' cellPadding='3' width='50%' border=0>" );
        buf.append( "<TR bgColor='#ccccff'><TD colspan='9' width='100%' align='center' ><B>NEW TRANSIT LOTS ARE ADDED TO THE CHIPTRAC SYSTEM <B></TD>" );
        buf.append( "<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'><B>SL NO.<B></TD><TD width='10%' align='CENTER'><B>CUSTOMER PRODUCT<B></TD><TD width='8%' align='CENTER'><B>LOT NUMBER<B></TD>" );
        buf.append( "<TD width='8%' align='CENTER'><B>SHIP DATE<B></TD> <TD width='8%' align='CENTER'><B>SHIP QTY<B></TD><TD width='8%' align='CENTER'><B>SHIP DIE QTY<B></TD>" );
        buf.append( "<TD width='8%' align='CENTER'><B>PROCESS<B></TD> <TD width='8%' align='CENTER'><B>FROM VENDOR<B></TD><TD width='8%' align='CENTER'><B>TO VENDOR<B></TD></TR>" );

        for ( int i = 0; i < newShipLotsList.size(); i++ )
        {
            fabStage = ( FabStage ) newShipLotsList.get( i );

            buf.append("<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'>").append(i + 1).append(".");
            buf.append("<TD width='10%' align='CENTER'>").append(fabStage.getDeviceName()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getLotNumber()).append("</TD>");
            //buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getFabLotNumber()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getShipDate()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getShipQty()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getShipDieQty() == null ? "":fabStage.getShipDieQty() ).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getProcess()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getVendorName()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append( fabStage.getShipToLocation() == null ? "": new String ( fabStage.getShipToLocation().getBytes("BIG5") )  ).append("</TD></TR>");
        }

        buf.append( "</TR></TABLE></BODY></HTML>\n" );
        String message = buf.toString();
        String subject = " Lots Shipped out from ";
        try
        {
            // SEND MESSAGE
            subject = process.toUpperCase() + subject + vendor.toUpperCase();
            Mailer.send ( from, to, null, null, subject, message, null, Mailer.CONTENT_TYPE_HTML, false );
            success = true;
        }
        catch( Exception e )
        {
            success = false;
           log("Exception while sending new Transit Lot mail notifaction");
        }
        return success;
    }
    public static void notifyUserAboutNewTransitLots( ArrayList<FabStage> newShipLotsList, String toEmailIds  ) throws Exception
    {
        if ( newShipLotsList == null || newShipLotsList.size() <= 0 )
        {
            return;
        }
        FabStage fabStage   = null;

        String from = ConfigManager.getScalarAdminEmail();

        int index = from.lastIndexOf("\\");
        from  = from.substring( index+1, from.length() );
        
        if ( toEmailIds == null || toEmailIds.length() == 0 )
        {
            log("Transit lots Notification not set up for users");
            return;
        }

        // FORMAT MESSAGE
        StringBuffer buf = new StringBuffer( 2098 );
        buf.append( "<HTML><HEAD><META http-equiv=Content-Type content='text/html; charset=BIG5'></HEAD><BODY>" );
        buf.append( "<TABLE ALIGN = 'CENTER' style='FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Verdana' borderColor='#ffffff' cellSpacing='1' cellPadding='3' width='50%' border=0>" );
        buf.append( "<TR bgColor='#ccccff'><TD colspan='10' width='100%' align='center' ><B>NEW TRANSIT LOTS ARE ADDED TO THE CHIPTRAC SYSTEM <B></TD>" );
        buf.append( "<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'><B>SL NO.<B></TD><TD width='10%' align='CENTER'><B>CUSTOMER PRODUCT<B></TD><TD width='8%' align='CENTER'><B>LOT NUMBER<B></TD>" );
        buf.append( "<TD width='8%' align='CENTER'><B>FAB LOT NUMBER<B></TD><TD width='8%' align='CENTER'><B>SHIP DATE<B></TD> <TD width='8%' align='CENTER'><B>SHIP QTY<B></TD><TD width='8%' align='CENTER'><B>SHIP DIE QTY<B></TD>" );
        buf.append( "<TD width='8%' align='CENTER'><B>PROCESS<B></TD> <TD width='8%' align='CENTER'><B>FROM VENDOR<B></TD><TD width='8%' align='CENTER'><B>TO VENDOR<B></TD></TR>" );

        for ( int i = 0; i < newShipLotsList.size(); i++ )
        {
            fabStage = ( FabStage ) newShipLotsList.get( i );

            buf.append("<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'>").append(i + 1).append(".");
            buf.append("<TD width='10%' align='CENTER'>").append(fabStage.getDeviceName()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getLotNumber()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getFabLotNumber()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getShipDate()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getShipQty()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getShipDieQty() == null ? "":fabStage.getShipDieQty() ).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getProcess()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getVendorName()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append( fabStage.getShipToLocation() == null ? "": new String ( fabStage.getShipToLocation().getBytes("BIG5") )  ).append("</TD></TR>");
        }

        buf.append( "</TR></TABLE></BODY></HTML>\n" );
        String message = buf.toString();
        String subject = " Lots shipped from vendors between " + DateUtil.getPreviousDate( 1, "MM/dd/yyyy HH:mm") + " and " +
                         DateUtil.getCurrentDate("MM/dd/yyyy HH:mm");
        try
        {
            // SEND MESSAGE
            Mailer.send ( from, toEmailIds, null, null, subject, message, null, Mailer.CONTENT_TYPE_HTML, false );
        }
        catch( Exception e )
        {
           log("Exception while sending new Transit Lot mail notifaction as of " + DateUtil.getCurrentDate());
        }
    }

    public static void notifyUserAboutLowYieldLots( ArrayList<FabStage> loeYieldLotsList, String toEmailIds , String process ) throws Exception
    {
        if ( loeYieldLotsList == null || loeYieldLotsList.size() <= 0 )
        {
            return;
        }
        FabStage fabStage   = null;
        String subject = null;

        String from = ConfigManager.getScalarAdminEmail();
        int index = from.lastIndexOf("\\");
        from  = from.substring( index+1, from.length() );

        if ( toEmailIds == null || toEmailIds.length() == 0 )
        {
            log("Low Yield lots Notification not set up for users");
            return;
        }
        if ( "FT".equalsIgnoreCase(process))
        {
            subject = "FT low yield lots between " + DateUtil.getPreviousDate( 1, "MM/dd/yyyy HH:mm") + " and " +
                         DateUtil.getCurrentDate("MM/dd/yyyy HH:mm");
        }
        else
        {
            subject = "WS low yield lots between " + DateUtil.getPreviousDate( 1, "MM/dd/yyyy HH:mm") + " and " +
                         DateUtil.getCurrentDate("MM/dd/yyyy HH:mm");
        }
        // FORMAT MESSAGE
        StringBuffer buf = new StringBuffer( 2098 );
        buf.append( "<HTML><HEAD><META http-equiv=Content-Type content='text/html; charset=BIG5'></HEAD><BODY>" );
        buf.append( "<TABLE ALIGN = 'CENTER' style='FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Verdana' borderColor='#ffffff' cellSpacing='1' cellPadding='3' width='50%' border=0>" );
        buf.append( "<TR bgColor='#ccccff'><TD colspan='9' width='100%' align='center' ><B>LOW YIELD LOTS FOUND IN THE CHIPTRAC SYSTEM <B></TD>" );
        buf.append( "<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'><B>SL NO.<B></TD><TD width='10%' align='CENTER'><B>CUSTOMER PRODUCT<B></TD>");
        buf.append( "<TD width='8%' align='CENTER'><B>LOT NUMBER<B></TD><TD width='8%' align='CENTER'><B>FAB LOT NUMBER<B></TD><TD width='8%' align='CENTER'><B>WAFER QTY<B></TD>");
        buf.append( "<TD width='8%' align='CENTER'><B>IN QTY<B></TD><TD width='8%' align='CENTER'><B>OUT QTY<B></TD><TD width='8%' align='CENTER'><B>YIELD<B></TD><TD width='6%' align='CENTER'><B>VENDOR<B></TD></TR>" );

        for ( int i = 0; i < loeYieldLotsList.size(); i++ )
        {
            fabStage = ( FabStage ) loeYieldLotsList.get( i );
            buf.append("<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'>").append(i + 1).append(".");
            buf.append("</TD><TD width='10%' align='CENTER'>").append(fabStage.getDeviceName()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getLotNumber()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getFabLotNumber()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getWaferQtyIn() == null ? "":fabStage.getWaferQtyIn() ).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getQtyIn()== null ? "":fabStage.getQtyIn() ).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getQtyOut() > 0 ? fabStage.getQtyOut() : "" ).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getYield()).append("</TD>");
            buf.append("<TD width='6%' align='CENTER'>").append(fabStage.getVendorName()).append("</TD></TR>");
        }
        buf.append( "</TR></TABLE></BODY></HTML>\n" );
        String message = buf.toString();

        try
        {
            // SEND MESSAGE
            Mailer.send ( from, toEmailIds, null, null, subject, message, null, Mailer.CONTENT_TYPE_HTML, false );
        }
        catch(Exception e)
        {
            //e.printStackTrace();
            log("Exception while sending Low Yield lots mail notifaction as of " + DateUtil.getCurrentDate());
        }
    }

    public static boolean notifyUserAboutLowYieldLots( ArrayList<FabStage> loeYieldLotsList, String vendor , String process, String nsType ) throws Exception
    {
        if ( loeYieldLotsList == null || loeYieldLotsList.size() <= 0 )
        {
            return false;
        }
        FabStage fabStage   = null;
        String subject = null;
        boolean  success     = false;

        String from = ConfigManager.getScalarAdminEmail();
        int index = from.lastIndexOf("\\");
        from  = from.substring( index+1, from.length() );

        String[]  emailArray = null;

        if ( LOCATION_TYPE_FT.equalsIgnoreCase( process ) || LOCATION_TYPE_TEST.equalsIgnoreCase( process ) )
        {
             emailArray = NotificationDAO.loadEmailsByNSType( vendor, LOCATION_TYPE_TEST, nsType );
        }
        else if ( LOCATION_TYPE_SORT.equalsIgnoreCase( process ) )
        {
             emailArray = NotificationDAO.loadEmailsByNSType( vendor, LOCATION_TYPE_SORT, nsType );
        }
        String toEmailIds = null;
        if ( emailArray != null && emailArray.length > 0 )
        {
            toEmailIds   = StringUtils.concatStrings ( emailArray, Mailer.EMAIL_SEPARATOR );
        }
        else
        {
            log("Loe Yield lots Notification not set up for users");
            return success;
        }

        subject = vendor.toUpperCase() + " low yield lots in " + process.toUpperCase();

        // FORMAT MESSAGE
        StringBuffer buf = new StringBuffer( 2098 );
        buf.append( "<HTML><HEAD><META http-equiv=Content-Type content='text/html; charset=BIG5'></HEAD><BODY>" );
        buf.append( "<TABLE ALIGN = 'CENTER' style='FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Verdana' borderColor='#ffffff' cellSpacing='1' cellPadding='3' width='50%' border=0>" );
        buf.append( "<TR bgColor='#ccccff'><TD colspan='9' width='100%' align='center' ><B>LOW YIELD LOTS FOUND IN THE CHIPTRAC SYSTEM <B></TD>" );
        buf.append( "<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'><B>SL NO.<B></TD><TD width='10%' align='CENTER'><B>CUSTOMER PRODUCT<B></TD>");
        buf.append( "<TD width='8%' align='CENTER'><B>LOT NUMBER<B></TD><TD width='8%' align='CENTER'><B>FAB LOT NUMBER<B></TD><TD width='8%' align='CENTER'><B>WAFER QTY<B></TD>");
        buf.append( "<TD width='8%' align='CENTER'><B>IN QTY<B></TD><TD width='8%' align='CENTER'><B>OUT QTY<B></TD><TD width='8%' align='CENTER'><B>YIELD<B></TD><TD width='6%' align='CENTER'><B>VENDOR<B></TD></TR>" );

        for ( int i = 0; i < loeYieldLotsList.size(); i++ )
        {
            fabStage = ( FabStage ) loeYieldLotsList.get( i );
            buf.append("<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'>").append(i + 1).append(".");
            buf.append("</TD><TD width='10%' align='CENTER'>").append(fabStage.getDeviceName()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getLotNumber()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getFabLotNumber()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getWaferQtyIn() == null ? "":fabStage.getWaferQtyIn() ).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getQtyIn()== null ? "":fabStage.getQtyIn() ).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getQtyOut() > 0 ? fabStage.getQtyOut() : "" ).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getYield()).append("</TD>");
            buf.append("<TD width='6%' align='CENTER'>").append(fabStage.getVendorName()).append("</TD></TR>");
        }
        buf.append( "</TR></TABLE></BODY></HTML>\n" );
        String message = buf.toString();

        try
        {
            // SEND MESSAGE
            Mailer.send ( from, toEmailIds, null, null, subject, message, null, Mailer.CONTENT_TYPE_HTML, false );
            success = true;
        }
        catch(Exception e)
        {
            success = false;
            //e.printStackTrace();
            log("Exception while sending Low Yield lots mail notifaction as of " + DateUtil.getCurrentDate());
        }
        return success;
    }
    /**
    * To send the when ever we have discrepancy lots (i.e. No valid part number in Chiptrac)
    * @param discrepancyList
    */
    public static boolean notifyUserAboutDiscrepancyLots( ArrayList<FabStage> discrepancyList, String vendor, String process, String nsType ) throws Exception
    {
        if ( discrepancyList == null || discrepancyList.size() <= 0 )
        {
            return false;
        }
        
        FabStage fabStage    = null;
        boolean  success     = false;

        String[]  emailArray = null;

        if ( LOCATION_TYPE_WAFER_BANK.equalsIgnoreCase( process ) )
        {
             emailArray = NotificationDAO.loadEmailsByNSType( vendor, LOCATION_TYPE_WAFER_STORE, nsType );
        }
        else if ( LOCATION_TYPE_FAB_OPTION.equalsIgnoreCase( process ) )
        {
             emailArray = NotificationDAO.loadEmailsByNSType( vendor, LOCATION_TYPE_FAB, nsType );
        }
        else if( LOCATION_TYPE_FT.equalsIgnoreCase( process )  || LOCATION_TYPE_BALLSCAN.equalsIgnoreCase( process )
                || LOCATION_TYPE_BAKING.equalsIgnoreCase( process ) ||  LOCATION_TYPE_FPO.equalsIgnoreCase( process )
                )
        {
              emailArray = NotificationDAO.loadEmailsByNSType( vendor, WIP_TYPE_TEST, nsType );
        }
        else if( LOCATION_TYPE_TAPE_REEL.equalsIgnoreCase( process ) || LOCATION_TYPE_EOL.equalsIgnoreCase( process )
                || LOCATION_TYPE_PRE_TEST.equalsIgnoreCase( process ) )
        {
              emailArray = NotificationDAO.loadEmailsByNSType( vendor, LOCATION_TYPE_CLASS_STORE, nsType );
        }
        else if( LOCATION_TYPE_MARK_AND_PACK.equalsIgnoreCase( process ) )
        {
              emailArray = NotificationDAO.loadEmailsByNSType( vendor, "Mark &amp; Pack", nsType );
        }
        else
        {
             emailArray = NotificationDAO.loadEmailsByNSType( vendor, process, nsType );
        }

        String from = ConfigManager.getScalarAdminEmail();
        int index = from.lastIndexOf("\\");
        from  = from.substring( index+1, from.length() );
        String to = null;
        if ( emailArray != null && emailArray.length > 0 )
        {
            to   = StringUtils.concatStrings ( emailArray, Mailer.EMAIL_SEPARATOR );
        }
        else
        {
            log("Discrepancy lots Notification not set up for users");
            return success;
        }
        String subject = vendor.toUpperCase() + " Discrepancy Lots In "+ process.toUpperCase();
                
        // FORMAT MESSAGE
        StringBuffer buf = new StringBuffer( 2098 );
        buf.append( "<HTML><HEAD><META http-equiv=Content-Type content='text/html; charset=BIG5'></HEAD><BODY>" );
        buf.append( "<TABLE ALIGN = 'CENTER' style='FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Verdana' borderColor='#ffffff' cellSpacing='1' cellPadding='3' width='50%' border=0>" );
        buf.append( "<TR bgColor='#ccccff'><TD colspan='8' width='100%' align='center' ><B>DISCREPANCY LOTS IN THE CHIPTRAC SYSTEM <B></TD>" );
        buf.append( "<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'><B>SL NO.<B></TD><TD width='10%' align='CENTER'><B>CUSTOMER PRODUCT<B></TD>" );
        buf.append( "<TD width='8%' align='CENTER'><B>LOT NUMBER<B></TD><TD width='8%' align='CENTER'><B>FAB LOT NUMBER<B></TD>" );
        buf.append( "<TD width='8%' align='CENTER'><B>WAFER QTY<B></TD><TD width='8%' align='CENTER'><B>DIE QTY<B></TD>" );
        buf.append( "<TD width='8%' align='CENTER'><B>PROCESS<B></TD><TD width='8%' align='CENTER'><B>VENDOR<B></TD></TR>" );

        for ( int i = 0; i < discrepancyList.size(); i++ )
        {
            fabStage = ( FabStage ) discrepancyList.get( i );

            buf.append("<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'>").append(i + 1).append(".");
            buf.append("<TD width='10%' align='CENTER'>").append(fabStage.getDeviceName()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getLotNumber()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getFabLotNumber()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getWaferQtyIn() == null ? "":fabStage.getWaferQtyIn() ).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getQtyIn()== null ? "":fabStage.getQtyIn() ).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getProcess()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getVendorName()).append("</TD></TR>");
        }

        buf.append( "</TR></TABLE></BODY></HTML>\n" );
        String message = buf.toString();

        try
        {
            // SEND MESSAGE
            Mailer.send ( from, to, null, null, subject, message, null, Mailer.CONTENT_TYPE_HTML, false );
            success = true;
        }
        catch(Exception e)
        {
            success = false;
            log("Exception while sending Discrepancy Lot mail notifaction");
        }

        return success;
    }

    public static void notifyUserAboutDiscrepancyLots( ArrayList<FabStage> discrepancyList, String toEmailIds ) throws Exception
    {
        if ( discrepancyList == null || discrepancyList.size() <= 0 )
        {
            return;
        }

        FabStage fabStage    = null;

        String from = ConfigManager.getScalarAdminEmail();
        int index = from.lastIndexOf("\\");
        from  = from.substring( index+1, from.length() );
        if ( toEmailIds == null || toEmailIds.length() == 0  )
        {
            log("Discrepancy lots Notification not set up for users");
            return;
        }

        String subject = "Discrepancy lots between " + DateUtil.getPreviousDate( 1, "MM/dd/yyyy HH:mm") + " and " +
                         DateUtil.getCurrentDate("MM/dd/yyyy HH:mm");

        // FORMAT MESSAGE
        StringBuffer buf = new StringBuffer( 2098 );
        buf.append( "<HTML><HEAD><META http-equiv=Content-Type content='text/html; charset=BIG5'></HEAD><BODY>" );
        buf.append( "<TABLE ALIGN = 'CENTER' style='FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Verdana' borderColor='#ffffff' cellSpacing='1' cellPadding='3' width='50%' border=0>" );
        buf.append( "<TR bgColor='#ccccff'><TD colspan='8' width='100%' align='center' ><B>DISCREPANCY LOTS IN THE CHIPTRAC SYSTEM <B></TD>" );
        buf.append( "<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'><B>SL NO.<B></TD><TD width='10%' align='CENTER'><B>CUSTOMER PRODUCT<B></TD>" );
        buf.append( "<TD width='8%' align='CENTER'><B>LOT NUMBER<B></TD><TD width='8%' align='CENTER'><B>FAB LOT NUMBER<B></TD>" );
        buf.append( "<TD width='8%' align='CENTER'><B>WAFER QTY<B></TD><TD width='8%' align='CENTER'><B>DIE QTY<B></TD>" );
        buf.append( "<TD width='8%' align='CENTER'><B>PROCESS<B></TD><TD width='8%' align='CENTER'><B>VENDOR<B></TD></TR>" );

        for ( int i = 0; i < discrepancyList.size(); i++ )
        {
            fabStage = ( FabStage ) discrepancyList.get( i );

            buf.append("<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'>").append(i + 1).append(".");
            buf.append("<TD width='10%' align='CENTER'>").append(fabStage.getDeviceName()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getLotNumber()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getFabLotNumber()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getWaferQtyIn() == null ? "":fabStage.getWaferQtyIn() ).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getQtyIn()== null ? "":fabStage.getQtyIn() ).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getProcess()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getVendorName()).append("</TD></TR>");
        }

        buf.append( "</TR></TABLE></BODY></HTML>\n" );
        String message = buf.toString();

        try
        {
            // SEND MESSAGE
            Mailer.send ( from, toEmailIds, null, null, subject, message, null, Mailer.CONTENT_TYPE_HTML, false );
        }
        catch(Exception e)
        {
            log("Exception while sending Discrepancy Lot mail notifaction as of " + DateUtil.getCurrentDate());
        }
    }

    /**
    * @param noPoList
    * @param process
    * @return  boolean
    */
    public static boolean notifyUserAboutNoPOLots( ArrayList<FabStage> noPoList, String vendor, String process, String nsType ) throws Exception
    {
        if ( noPoList == null || noPoList.size() <= 0 )
        {
            return false;
        }

        FabStage fabStage    = null;
        boolean  success     = false;
        String[]  emailArray = null;

        if ( LOCATION_TYPE_WAFER_BANK.equalsIgnoreCase( process ) )
        {
             emailArray = NotificationDAO.loadEmailsByNSType( vendor, LOCATION_TYPE_WAFER_STORE, nsType );
        }
        else if ( LOCATION_TYPE_FAB_OPTION.equalsIgnoreCase( process ) )
        {
             emailArray = NotificationDAO.loadEmailsByNSType( vendor, LOCATION_TYPE_FAB, nsType );
        }
        else if( LOCATION_TYPE_FT.equalsIgnoreCase( process )  || LOCATION_TYPE_BALLSCAN.equalsIgnoreCase( process )
                || LOCATION_TYPE_BAKING.equalsIgnoreCase( process ) ||  LOCATION_TYPE_FPO.equalsIgnoreCase( process )
                )
        {
              emailArray = NotificationDAO.loadEmailsByNSType( vendor, WIP_TYPE_TEST, nsType );
        }
        else if( LOCATION_TYPE_TAPE_REEL.equalsIgnoreCase( process ) || LOCATION_TYPE_EOL.equalsIgnoreCase( process )
                || LOCATION_TYPE_PRE_TEST.equalsIgnoreCase( process ) )
        {
              emailArray = NotificationDAO.loadEmailsByNSType( vendor, LOCATION_TYPE_CLASS_STORE, nsType );
        }
        else
        {
             emailArray = NotificationDAO.loadEmailsByNSType( vendor, process, nsType );
        }

        String from = ConfigManager.getScalarAdminEmail();
        int index = from.lastIndexOf("\\");
        from  = from.substring( index+1, from.length() );
        String to   = StringUtils.concatStrings ( emailArray, Mailer.EMAIL_SEPARATOR );
        String subject = "No PO Number Lots In "+ vendor.toUpperCase() + " "+ process.toUpperCase();

        // FORMAT MESSAGE
        StringBuffer buf = new StringBuffer( 2098 );
        buf.append( "<HTML><HEAD><META http-equiv=Content-Type content='text/html; charset=BIG5'></HEAD><BODY>" );
        buf.append( "<TABLE ALIGN = 'CENTER' style='FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Verdana' borderColor='#ffffff' cellSpacing='1' cellPadding='3' width='50%' border=0>" );
        buf.append( "<TR bgColor='#ccccff'><TD colspan='6' width='100%' align='center' ><B>NO PO NUMBER LOTS IN THE CHIPTRAC SYSTEM <B></TD>" );
        buf.append( "<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'><B>SL NO.<B></TD><TD width='8%' align='CENTER'><B>CUSTOMER PRODUCT<B></TD><TD width='8%' align='CENTER'><B>LOT NUMBER<B></TD><TD width='8%' align='CENTER'><B>FAB LOT NO<B></TD>" );
        buf.append( "<TD width='8%' align='CENTER'><B>PROCESS<B></TD><TD width='8%' align='CENTER'><B>VENDOR<B></TD></TR>" );

        for ( int i = 0; i < noPoList.size(); i++ )
        {
            fabStage = ( FabStage ) noPoList.get( i );

            buf.append("<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'>").append(i + 1).append(".");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getDeviceName()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getLotNumber()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getFabLotNumber()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getProcess()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getVendorName()).append("</TD></TR>");
        }

        buf.append( "</TR></TABLE></BODY></HTML>\n" );
        String message = buf.toString();

        try
        {
            // SEND MESSAGE
            Mailer.send ( from, to, null, null, subject, message, null, Mailer.CONTENT_TYPE_HTML, false );
            success = true;
        }
        catch(Exception e)
        {
            success = false;
            log("Exception while sending No PO Number mail notifaction");
        }

        return success;
    }

    /**
     *
     * @param noAssemblyVendorList
     * @param vendor
     * @param process
     * @param nsType
     * @return
     * @throws Exception
     */
    public static boolean notifyUserAboutNoAssemblyVendorLots(ArrayList<FabStage> noAssemblyVendorList, String vendor, String process, String nsType) throws Exception
    {
        if (noAssemblyVendorList == null || noAssemblyVendorList.size() <= 0) {
            return false;
        }

        FabStage fabStage = null;
        boolean success = false;
        String[]  emailArray = null;

        if (LOCATION_TYPE_FINISHED_GOODS.equalsIgnoreCase(process))
        {
            emailArray = NotificationDAO.loadEmailsByNSType(vendor, LOCATION_TYPE_FINISHED_GOODS, nsType);
        }

        String from = ConfigManager.getScalarAdminEmail();
        int index = from.lastIndexOf("\\");
        from = from.substring(index + 1, from.length());
        String to = StringUtils.concatStrings(emailArray, Mailer.EMAIL_SEPARATOR);
        String subject = "No Assembly Location Vendor Lots In " + vendor.toUpperCase() + " " + process.toUpperCase();

        // FORMAT MESSAGE
        StringBuffer buf = new StringBuffer(2098);
        buf.append("<HTML><HEAD><META http-equiv=Content-Type content='text/html; charset=BIG5'></HEAD><BODY>");
        buf.append("<TABLE ALIGN = 'CENTER' style='FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Verdana' borderColor='#ffffff' cellSpacing='1' cellPadding='3' width='50%' border=0>");
        buf.append("<TR bgColor='#ccccff'><TD colspan='5' width='100%' align='center' ><B>NO ASSEMBLY LOCATION VENDOR IN THE CHIPTRAC SYSTEM <B></TD>");
        buf.append("<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'><B>SL NO.<B></TD><TD width='8%' align='CENTER'><B>CUSTOMER PRODUCT<B></TD><TD width='8%' align='CENTER'><B>LOT NUMBER<B></TD>");
        buf.append("<TD width='8%' align='CENTER'><B>PROCESS<B></TD><TD width='8%' align='CENTER'><B>VENDOR<B></TD></TR>");

        for (int i = 0; i < noAssemblyVendorList.size(); i++) {
            fabStage = (FabStage) noAssemblyVendorList.get(i);

            buf.append("<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'>").append(i + 1).append(".");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getDeviceName()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getLotNumber()).append("</TD>");
            //buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getFabLotNumber()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getProcess()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getVendorName()).append("</TD></TR>");
        }

        buf.append("</TR></TABLE></BODY></HTML>\n");
        String message = buf.toString();

        try {
            // SEND MESSAGE
            Mailer.send(from, to, null, null, subject, message, null, Mailer.CONTENT_TYPE_HTML, false);
            success = true;
        }
        catch (Exception e) {
            success = false;
            log("Exception while sending No Assembly Vendor Lots mail notifaction");
        }

        return success;
    }

    public static boolean notifyUserAboutDropOutLots( ArrayList<FabStage> newLotsList, String nsType, String toEmils ) throws Exception
    {
        if ( newLotsList == null || newLotsList.size() <= 0 )
        {
            return false;
        }

        FabStage fabStage   = null;
        boolean success     = false;
        String[] emailArray = null;
        if ( toEmils == null || toEmils.trim().length() == 0 )
        {
            emailArray = NotificationDAO.loadEmailsByNSType( null, null, nsType );
            if ( emailArray != null && emailArray.length > 0 )
            {
                toEmils   = StringUtils.concatStrings ( emailArray, Mailer.EMAIL_SEPARATOR );
            }
            else
            {
                log("DropOut lots Notification not set up for users");
                return success;
            }
        }

        String from = ConfigManager.getScalarAdminEmail();
        int index = from.lastIndexOf("\\");
        from  = from.substring( index+1, from.length() );

        String subject = "Lots Dropped out from Vendors";

        // FORMAT MESSAGE
        StringBuffer buf = new StringBuffer( 2098 );
        buf.append( "<HTML><HEAD><META http-equiv=Content-Type content='text/html; charset=BIG5'></HEAD><BODY>" );
        buf.append( "<TABLE ALIGN = 'CENTER' style='FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Verdana' borderColor='#ffffff' cellSpacing='1' cellPadding='3' width='50%' border=0>" );
        buf.append( "<TR bgColor='#ccccff'><TD colspan='8' width='100%' align='center' ><B>LOTS DROPPED OUT FROM VENDORS <B></TD>" );
        buf.append( "<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'><B>SL NO.<B></TD><TD width='10%' align='CENTER'><B>CUSTOMER PRODUCT<B></TD>");
        buf.append( "<TD width='8%' align='CENTER'><B>LOT NUMBER<B></TD><TD width='8%' align='CENTER'><B>FAB LOT NUMBER<B></TD>");
        buf.append( "<TD width='8%' align='CENTER'><B>LOT QTY<B></TD><TD width='8%' align='CENTER'><B>PROCESS<B></TD><TD width='8%' align='CENTER'><B>VENDOR<B></TD><TD width='4%' align='CENTER'><B>TYPE<B></TD></TR>" );

        for ( int i = 0; i < newLotsList.size(); i++ )
        {
            fabStage = ( FabStage ) newLotsList.get( i );
            buf.append("<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'>").append(i + 1).append(".");
            buf.append("</TD><TD width='10%' align='CENTER'>").append(fabStage.getDeviceName()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getLotNumber()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getFabLotNumber()).append("</TD>");
            //buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getWaferQtyIn() == null ? "":fabStage.getWaferQtyIn() ).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getQtyIn()== null ? "":fabStage.getQtyIn() ).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getProcess()).append("</TD>");
            buf.append("<TD width='8%' align='CENTER'>").append(fabStage.getVendorName()).append("</TD>");
            buf.append("<TD width='4%' align='CENTER'>").append(fabStage.getSequence()).append("</TD></TR>");
        }
        buf.append( "</TR></TABLE></BODY></HTML>\n" );
        String message = buf.toString();

        try
        {
            // SEND MESSAGE
            Mailer.send ( from, toEmils, null, null, subject, message, null, Mailer.CONTENT_TYPE_HTML, false );
            success = true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            success = false;
            log("Exception while sending new Lot mail notification");
        }

        return success;
    }


    /**
     * @throws Exception
     */
    public static void closeDropOutLots(String vendorName) throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("{ call SP_CLOSE_ALL_DROP_OUT_LOTS( ? )} ");
            pstmt.setString(1, vendorName);
            pstmt.executeUpdate();
            success = true;
        }
        catch (Exception sqle)
        {
            success = false;
            sqle.printStackTrace();
            throw new ScalarException("Could not load all dropped Lots from the database", sqle);
        }
        finally
        {
            // Commit/Rollback here it self -- if its not in a manual transaction
            try
            {
                if (conn != null)
                {
                    if (success )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore)
            {
                ignore.printStackTrace();
            }
            if (rset != null)
            {
                try
                {
                    rset.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }

    public static void saveCostCodes(Iterator<CostCodeStatus> costCodesIterator) throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement("{ call SP_CREATE_UPDATE_COST_CODES( ?, ?  )} ");

            CostCodeStatus costCodeStatus = null;

            while ( costCodesIterator.hasNext() )
            {
                costCodeStatus = (CostCodeStatus) costCodesIterator.next();

                pstmt.setString( 1, costCodeStatus.getCost() );
    			pstmt.setString( 2, costCodeStatus.getMktPartNumber() );

                pstmt.executeUpdate();
            }

            success = true;
        }
        catch (Exception sqle)
        {
            success = false;
            sqle.printStackTrace();
            throw new ScalarException("Could not update cost codes in to database", sqle);
        }
        finally
        {
            // Commit/Rollback here it self -- if its not in a manual transaction
            try
            {
                if (conn != null)
                {
                    if ( success )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore)
            {
                ignore.printStackTrace();
            }

            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }

	public static ArrayList<InTransitLots> loadAllTransitLotsByProcess( String psType, String lotNumber, String nextLocProcess ) throws ScalarException
    {
        Connection conn             = null;
        PreparedStatement pstmt     = null;
        ArrayList<InTransitLots> transitLotList    = new ArrayList<InTransitLots>();
        ResultSet rset              = null;
        InTransitLots inTransitLots = null;
        try
        {
            conn = DataSource.getConnection();
            
            String query = "select * from fn_load_all_transit_lots( 0 ) WHERE 1=1 ";

            if (psType != null )
            {
                query = query + " AND PS_TYPE = '" + psType + "'";
            }
            if ( lotNumber != null )
            {
                query = query + " AND LOT_NUMBER = '" + lotNumber + "'";
            }
            if ( nextLocProcess != null )
            {
                query = query + " AND NEXT_LOC IN (SELECT L_VENDOR_NAME FROM LOCATION WHERE L_TYPE = '" + nextLocProcess + "')";
            }
            pstmt = conn.prepareStatement( query );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                inTransitLots = new InTransitLots();
                inTransitLots.setLotNumber( rset.getString( "Lot_Number" ) );
                inTransitLots.setShippedLotID( rset.getString( "ShippedLot_Id" ) );
                inTransitLots.setWaferId( rset.getString( "WAFER_ID" ) );
                transitLotList.add( inTransitLots );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load all Lotnumbers from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return transitLotList;
    }

    public static int getApprovedAssemblyReleaseLot( String lotNumber, String partNumber , String vendorName ) throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        PreparedStatement pstmt1 = null;
        ResultSet rset          = null;
        ResultSet rset1          = null;
        int count               = -1;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "SELECT COUNT(*) AS RELEASE_COUNT FROM ASSM_RELEASE_TABLE WHERE ASSEMBLY_RELEASE_NO Like ? " +
                    "AND TO_VENDOR_ID = ( SELECT VENDOR_ID FROM VENDOR WHERE V_NAME = ? ) " +
                    "AND Processstep_Id IN ( SELECT PS.ProcessStep_Id FROM Product P, Process_Step PS WHERE P.ProcessStep_Id = PS.ProcessStep_ID AND Part_Number = ?  " +
                    "AND PS_Type_Name = 'Assembly' ) AND STATUS = 'APPROVED' " );
            pstmt.setString( 1, lotNumber );
            pstmt.setString( 2, vendorName );
            pstmt.setString( 3, partNumber );

            pstmt1 = conn.prepareStatement( "SELECT COUNT(*) AS RELEASE_COUNT FROM ASSM_RELEASE_TABLE WHERE ASSEMBLY_RELEASE_NO Like ? " +
                    "AND TO_VENDOR_ID = ( SELECT VENDOR_ID FROM VENDOR WHERE V_NAME = ? ) " +
                    "AND Processstep_Id IN ( select PS.ProcessStep_ID from Product_Map_Table PMT,Process_Step PS where PMT.ProcessStep_ID=PS.ProcessStep_ID AND Part_Number = ?  " +
                    "AND PS_Type_Name = 'Assembly' ) AND STATUS = 'APPROVED' " );

            pstmt1.setString( 1, lotNumber );
            pstmt1.setString( 2, vendorName );
            pstmt1.setString( 3, partNumber );

            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                count   = rset.getInt( "RELEASE_COUNT" );
            }
            else
            {
                  rset1 = pstmt1.executeQuery();

                  if ( rset1.next() )
                  {                                                 
                     count   = rset1.getInt( "RELEASE_COUNT" );
                  }
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load Assembly Release Lots from the database", sqle );
        }
        finally
        {
            if ( rset1 != null )
            {
                try
                {
                    rset1.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt1 != null )
            {
                try
                {
                    pstmt1.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return count;
    }

    public static void updateAssemblyReleaseStatus(String releaseNo) throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement("update ASSM_RELEASE_TABLE set status = 'Closed' where ASSEMBLY_RELEASE_NO like ? ");
            pstmt.setString(1, releaseNo);

            pstmt.executeUpdate();


            success = true;
        }
        catch (Exception sqle)
        {
            success = false;
            sqle.printStackTrace();
            throw new ScalarException("Could not update cost codes in to database", sqle);
        }
        finally
        {
            // Commit/Rollback here it self -- if its not in a manual transaction
            try
            {
                if (conn != null)
                {
                    if ( success )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore)
            {
                ignore.printStackTrace();
            }

            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }


    public static String getPartNumberByLotNo(String lotNo, String type) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        String partNumber = null;

        try
        {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement("SELECT TOP 1 P.PART_NUMBER  FROM PRODUCT P,PROCESS_STEP PS, LOT_TABLE LT \n" +
                    "WHERE P.PROCESSSTEP_ID=PS.PROCESSSTEP_ID AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID AND PS_TYPE_NAME = ? AND LOT_NUMBER = ? AND OBSOLETE != 0 ORDER BY P.PART_NUMBER");

            pstmt.setString(1, type);
            pstmt.setString(2, lotNo );
            
            rs = pstmt.executeQuery();

            while (rs.next())
            {
                partNumber = rs.getString("PART_NUMBER");
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not get Part Number by type", sql);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                }
                catch (SQLException ignore) {
                }
            }
        }
        return partNumber;
    }

    public static void saveInTransactionData( DBTransaction dbTransaction, Iterator assemblyLotIterator)  throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( " { CALL SP_CREATE_UPDATE_RECEIVE_TRANSACTION_LOT ( ?, ?, ?, ?, ?, ?, ?, ? ) }" );
            AssemblyLot assemblyLot = null;
            log( "Saving Lot Transaction In Qty Information... " );
            while( assemblyLotIterator.hasNext() )
            {
                assemblyLot = ( AssemblyLot ) assemblyLotIterator.next();
				log( assemblyLot + "\n" );
                if (assemblyLot.getLotNumber() == null) continue;

                //log( assemblyLot + "\n" );

                pstmt.setString( 1, assemblyLot.getDeviceName() );
                pstmt.setString( 2, assemblyLot.getLotNumber() );
                pstmt.setString( 3, assemblyLot.getDateIn() );
                pstmt.setString( 4, assemblyLot.getQtyIn() );
                pstmt.setString( 5, assemblyLot.getVendorName() );
                pstmt.setString( 6, assemblyLot.getFabLotNumber() );
                pstmt.setString( 7, assemblyLot.getDateCode() );
                pstmt.setString( 8, assemblyLot.getProcessType() );

                pstmt.addBatch();
            }

            pstmt.executeBatch();
            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not save Lot Transaction In qty Information into database", sqle );
        }

        finally
        {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }

            if( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch( SQLException ignore )
                {
                }
            }
            if( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch( SQLException ignore )
                {
                }
            }
        }

    }
    public static void saveOutTransactionData( DBTransaction dbTransaction, Iterator shipLotIterator)  throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( " { CALL SP_CREATE_UPDATE_OUT_TRANSACTION_LOT ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,? ) }" );
            ShippedLot shippedLot = null;
            log( "Saving Lot Transaction Out Qty Information... " );
            while( shipLotIterator.hasNext() )
            {
                shippedLot = ( ShippedLot ) shipLotIterator.next();

                if (shippedLot.getLotNumber() == null) continue;

                log( shippedLot + "\n" );

                pstmt.setString( 1, shippedLot.getPartNumber() );
                pstmt.setString( 2, shippedLot.getLotNumber() );
                pstmt.setString( 3, shippedLot.getShipDate() );
                pstmt.setString( 4, shippedLot.getShipQty() );
                pstmt.setString( 5, shippedLot.getVendorName() );
                pstmt.setString( 6, shippedLot.getFabLotNumber() );
                pstmt.setString( 7, shippedLot.getCurrentLocation() );
                pstmt.setString( 8, shippedLot.getProcessType() );
                pstmt.setString( 9, shippedLot.getTransactionType() );
                pstmt.setString( 10, shippedLot.getNextLocation() );
                pstmt.setString( 11, shippedLot.getNextProcess() );
                pstmt.setString( 12, shippedLot.getBatchingLot() );
                pstmt.setString( 13, shippedLot.getWaferID() );

                pstmt.addBatch();
            }

            pstmt.executeBatch();
            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not save Lot Transaction Out qty Information into database", sqle );
        }

        finally
        {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }

            if( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch( SQLException ignore )
                {
                }
            }
            if( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch( SQLException ignore )
                {
                }
            }
        }

    }

    public static void updatePackingSlipData( DBTransaction dbTransaction, Iterator psIterator)  throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "UPDATE Customer_Shipping_Table SET CST_BOX_COUNT = ?, CST_WEIGHT = ?, CST_AW_Bill_Number= ?, CST_MAWB= ?, CST_HAWB = ?, CST_ETD = ? , CST_ETA = ?  WHERE Label = ?  " );
            PackingSlipForm packingSlipForm = null;
            log( "Updateing Packing slip Information... " );
            while( psIterator.hasNext() )
            {
                packingSlipForm = ( PackingSlipForm ) psIterator.next();

                pstmt.setString( 1, packingSlipForm.getNoOfBoxes() );
                pstmt.setString( 2, packingSlipForm.getWeight() );
                pstmt.setString( 3, packingSlipForm.getAirWayBillNo() );
                pstmt.setString( 4, packingSlipForm.getMawb() );
                pstmt.setString( 5, packingSlipForm.getHawb() );
                pstmt.setString( 6, packingSlipForm.getEtd() );
                pstmt.setString( 7, packingSlipForm.getEta() );
                pstmt.setString( 8, packingSlipForm.getLabel() );

                pstmt.executeUpdate();
            }

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not save Lot Transaction In qty Information into database", sqle );
        }

        finally
        {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }

            if( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch( SQLException ignore )
                {
                }
            }
            if( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch( SQLException ignore )
                {
                }
            }
        }

    }

    public static void updateEDITransactionsData(DBTransaction dbTransaction, ArrayList transDataList) throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "INSERT INTO Lot_Transaction_Table values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)  " );
            Adaptor adaptor = null;
            log( "Updateing Transactions Information... " );
            for( int i=0; i<transDataList.size(); i++ )
            {
                adaptor = (Adaptor) transDataList.get(i);

                pstmt.setString( 1, adaptor.getTransactionCode() );
                pstmt.setString( 2, adaptor.getCustomerPart() );
                pstmt.setString( 3, adaptor.getWaferLot() );
                pstmt.setInt( 4, adaptor.getWaferNo() );
                pstmt.setInt( 5, adaptor.getDieQty() );
                pstmt.setString( 6, adaptor.getTransactionTime() );
                pstmt.setString( 7, adaptor.getLotStatus() );
                pstmt.setString( 8, adaptor.getWaferMapName() );
                pstmt.setString( 9, adaptor.getSystemTime() );
                pstmt.setString( 10, adaptor.getShipFromPlant() );
                pstmt.setString( 11, adaptor.getShipLocation() );
                pstmt.setString( 12, adaptor.getAssemblyLot() );
                pstmt.setInt( 13, adaptor.getLotDieQty() );
                pstmt.setString( 14, adaptor.getBachingLot() );
                pstmt.setString( 15, adaptor.getDateCode() );
                pstmt.setString( 16, adaptor.getVendorName() );

                pstmt.executeUpdate();
            }

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not save Lot Transaction Information into database", sqle );
        }

        finally
        {

            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }

            if( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch( SQLException ignore )
                {
                }
            }
            if( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch( SQLException ignore )
                {
                }
            }
        }
    }
    
    private static void log (String message)
    {
        if ( message == null) return;

        if (s_logger != null)
        {
            synchronized (s_logger)
            {
                s_logger.logDebug (message + LINE_SEPARATOR);
            }
        }
        else
        {
            System.out.println (message);
        }
    }

    public static void setLogger (Logger logger)
    {
        s_logger = logger;
    }

}